package JDBC_Test.park.Dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import JDBC_Test.park.JDBCUtil;
import JDBC_Test.park.Model.ParkRecordModel;

public class RecordDao {
	Connection con = null;
	Statement st = null;
	PreparedStatement ps = null;

	/**
	 * 插入停车记录
	 * 
	 * @param parkRecordModel
	 * @throws SQLException
	 */
	public void insert(ParkRecordModel parkRecordModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into record (park_no,car_license,start_date,end_date,money) values(?,?,?,?,?)";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setInt(1, parkRecordModel.getParkNo());
			ps.setString(2, parkRecordModel.getCarLicense());
			ps.setTimestamp(3, new java.sql.Timestamp(parkRecordModel.getStartTime().getTime()));
			ps.setTimestamp(4, null);
			ps.setDouble(5, 0);
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	/**
	 * 通过车牌号查询停车时的时间
	 * 
	 * @param parkRecordModel
	 * @return
	 * @throws SQLException
	 */
	public Timestamp selectByCarForTime(ParkRecordModel parkRecordModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select start_date from record where car_license = ?";
		Timestamp t = null;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, parkRecordModel.getCarLicense());
			ResultSet rs = null;
			rs = ps.executeQuery();
			while (rs.next()) {
				t = rs.getTimestamp("start_date");
			}
			return t;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
		return t;
	}

	/**
	 * 通过车号查询停车位
	 * 
	 * @param parkRecordModel
	 * @return
	 * @throws SQLException
	 */
	public Integer selectByCarForParkId(ParkRecordModel parkRecordModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select park_no from record where car_license = ?";
		Integer parkNo = 0;
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, parkRecordModel.getCarLicense());
			ResultSet rs = null;
			rs = ps.executeQuery();
			while (rs.next()) {
				parkNo = rs.getInt("park_no");
			}
			return parkNo;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
		return parkNo;
	}

	/**
	 * 取车时更新停车记录
	 * 
	 * @param parkRecordModel1
	 * @throws SQLException
	 */
	public void update(ParkRecordModel parkRecordModel1) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "update record set end_date = ? ,money = ? where car_license = ?";

		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, new java.sql.Timestamp(parkRecordModel1.getEndTime().getTime()));
			ps.setDouble(2, parkRecordModel1.getMoney());
			ps.setString(3, parkRecordModel1.getCarLicense());
			ps.executeUpdate();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
	}

	/**
	 * 查询所有收费记录
	 * 
	 * @throws SQLException
	 */
	public void selectAll() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from record";
		try {
			con = JDBCUtil.getConnection();
			st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				ParkRecordModel p = new ParkRecordModel();
				p.setParkNo(rs.getInt("park_no"));
				p.setCarLicense(rs.getString("car_license"));
				p.setStartTime(rs.getTimestamp("start_date"));
				p.setEndTime(rs.getTimestamp("end_date"));
				p.setMoney(rs.getDouble("money"));
				System.out.println(p.toString());
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

}
